using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using Tino.Core.Data;

namespace Tino.Core
{
    public class Entry : ISqliteTable
    {
        public Entry() { }

        #region Public members

        /// <summary>
        /// Returns all the entries for the given tags ids.
        /// </summary>
        /// <param name="TagIDs"></param>
        /// <returns></returns>
        public DataTable GetEntries(int[] TagIDs)
        {
            string tags = "";
            int count = 0;

            StringBuilder SQL = new StringBuilder("SELECT E.[ID], E.Title, E.CreationDate, " + 
                "E.ModificationDate, E.ImageIndex FROM Entry E");
            
            // If no tags selected, get 'em all.
            if (TagIDs != null && TagIDs.Length > 0)
            {
                foreach (int i in TagIDs)
                {
                    if (tags != "") tags += ", ";
                    tags += i.ToString();
                }
                count = TagIDs.Length;

                SQL.Append(" WHERE E.[ID] IN " +
                        "( " +
                        "   SELECT TE.EntryID FROM TaggedEntry TE WHERE TagID in ({0}) " +
                        "   GROUP BY TE.EntryID HAVING count(TE.EntryID) = {1} " +
                        ")");
            }
            SQL.Append(" ORDER BY E.Title DESC;");

            return SqliteAdapter.Get().Execute(string.Format(SQL.ToString(), tags, count));
        }

        /// <summary>
        /// Returns all the fields for a given entry ID
        /// </summary>
        /// <param name="EntryID"></param>
        /// <returns></returns>
        public DataTable GetEntry(int EntryID)
        {
            string SQL = "SELECT Title, Content, CreationDate, ModificationDate, ImageIndex FROM Entry WHERE ID = @ID;";
            DataTable dt = SqliteAdapter.Get().Execute(SQL, "@ID", EntryID);
            
            if (dt.Rows.Count != 1)
                throw new ArgumentException();

            return dt;
        }

        public int GetEntryImageIndex(int EntryID)
        {
            return Convert.ToInt32(GetEntry(EntryID).Rows[0]["ImageIndex"]);
        }

        /// <summary>
        /// Returns all the Tags for a given entry ID
        /// </summary>
        /// <param name="EntryID"></param>
        /// <returns></returns>
        public DataTable GetEntryTags(int EntryID)
        {
            string SQL = "SELECT t.ID, t.Name FROM TagElement t, TaggedEntry te WHERE t.ID = te.TagID AND te.EntryID = @EntryID;";
            return SqliteAdapter.Get().Execute(SQL, "@EntryID", EntryID);
        }

        /// <summary>
        /// Adds a new entry in the database.
        /// </summary>
        /// <param name="Title"></param>
        /// <param name="Content"></param>
        /// <param name="TagsIDs"></param>
        /// <returns></returns>
        public int AddEntry(string Title, string Content, int ImageIndex, int[] TagsIDs)
        {
            string SQL = "INSERT INTO Entry (Title, Content, CreationDate, ModificationDate, ImageIndex) VALUES (@Title, @Content, @CreationDate, @ModificationDate, @ImageIndex);";
            int newEntryID = -1;
            try
            {
                SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Title", Title, "@Content", Content, "@CreationDate", DateTime.Now.ToString("g"),
                            "@ModificationDate", DateTime.Now.ToString("g"), "@ImageIndex", ImageIndex);
                newEntryID = Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar("SELECT MAX(ID) FROM Entry"));
            }
            catch (Exception)
            {
                // Make sure we don't continue if the entry could't be created
                throw;
            }

            try
            {
                handleTags(newEntryID, TagsIDs);
            }
            catch (Exception)
            {
                throw;
            }
            
            // Everything went smooth, returns the new entry ID
            return newEntryID;
        }

        public void UpdateEntry(int EntryID, string Title, string Content, int ImageIndex, int[] TagsIDs)
        {
            string SQL = "UPDATE Entry SET Title = @Title, Content = @Content, ModificationDate = @ModificationDate, ImageIndex = @ImageIndex WHERE ID = @ID;";
            try
            {
                SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Title", Title, "@Content", Content, 
                    "@ModificationDate", DateTime.Now.ToString("g"), "@ImageIndex", ImageIndex, "@ID", EntryID);
            }
            catch (Exception)
            {
                throw;
            }

            // Update now the Tags list
            handleTags(EntryID, TagsIDs);
        }

        public void UpdateEntryImageIndex(int EntryID, int ImageIndex)
        {
            string SQL = "UPDATE Entry SET ImageIndex = @ImageIndex WHERE ID = @ID;";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ImageIndex", ImageIndex, "@ID", EntryID);
        }

        public void DeleteEntry(int EntryID)
        {
            string SQL = "DELETE FROM Entry WHERE ID = @ID;";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ID", EntryID);

            // Then delete relation ...
            SQL = "DELETE FROM TaggedEntry WHERE EntryID = @EntryID;";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@EntryID", EntryID);
        }

        #endregion

        #region Private members

        void handleTags(int EntryID, int[] TagsIDs)
        {
            // Purge first the list
            string SQL = "DELETE FROM TaggedEntry WHERE EntryID = @EntryID;";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@EntryID", EntryID);

            // Insert the tags ids reference now ...
            foreach (int i in TagsIDs)
            {
                SQL = "INSERT INTO TaggedEntry (EntryID, TagID) VALUES (@EntryID, @TagID);";
                SqliteAdapter.Get().ExecuteNonQuery(SQL, "@EntryID", EntryID, "@TagID", i);
            }
        }

        #endregion        

        #region ISqliteTable Members

        public void CreateTable()
        {
            StringBuilder sb = new StringBuilder("CREATE TABLE [Entry] ( ");
            sb.Append("ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,");
            sb.Append("Title VARCHAR NOT NULL, ");
            sb.Append("Content VARCHAR, ");
            sb.Append("CreationDate VARCHAR NOT NULL, ");
            sb.Append("ModificationDate VARCHAR NOT NULL, ");
            sb.Append("ImageIndex INTEGER DEFAULT '-1' NOT NULL ) ");

            SqliteAdapter.Get().ExecuteNonQuery(sb.ToString());
        }

        #endregion
    }
}
